/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package repository;

import Repository.IRepositoryProxy;
import DataModel.EntryStatus;
import DataModel.NotaryCertificate;
import DataModel.NotaryEntry;
import Repository.CertificateConflict;
import Utils.ByteArrayConverter;
import java.net.Inet4Address;
import java.net.InetAddress;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.joda.time.DateTime;
import org.joda.time.MutableDateTime;

/**
 *
 * @author Tomasz Nowak
 */
public class RepositoryProxy implements IRepositoryProxy
{
	InetAddress address;
	int port;
	String database;
	String username;
	String password;

	String connectionUrl;
	static String urlFormatString = "jdbc:sqlserver://%s:%d;databaseName=%s;user=%s;password=%s";
	static String selectByNameFormat = "SELECT * FROM dbo.Entities WHERE Name = '%s'";
	static String insertEntryFormat = "INSERT INTO dbo.Entities ( Name, PublicKey, Date, EntryStatus ) VALUES ( '%s',  CONVERT( BINARY(2048), %s , 0),'%s', %d)";
	static String containsCertificate = "SELECT COUNT(Name) FROM dbo.Entities WHERE Name = '%s' AND PublicKey = 'CONVERT( BINARY(2048), %s , 0)'";
	static String updateEntryStatus = "UPDATE dbo.Entities SET EntryStatus = %d, Date = '%s' WHERE Name = '%s' AND PublicKey = CONVERT( BINARY(2048), %s , 0) AND EntryStatus = %d";
	Connection connection;

	public RepositoryProxy( RepositoryProxySettings repositoryProxySettings ) throws ClassNotFoundException
	{
		address = repositoryProxySettings.getAddress();
		port = repositoryProxySettings.getPort();
		database = repositoryProxySettings.getDatabase();
		username = repositoryProxySettings.getUsername();
		password = repositoryProxySettings.getPassword();

		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	}

	@Override
	public void saveEntry( NotaryEntry entry )
	{
		Statement statement = null;
		try
		{
			statement = connection.createStatement();
			updateQuery(statement, prepareInsertStatement(entry));
		}
		catch ( SQLException ex )
		{
			Logger.getLogger(RepositoryProxy.class.getName()).log(Level.SEVERE, null, ex);
		}
		finally
		{
			if ( statement != null )
			{
				try
				{
					statement.close();
				}
				catch ( Exception e )
				{
				}
			}
		}
	}

	@Override
	public NotaryEntry getEntry( String name )
	{
		NotaryEntry entry = null;
		ResultSet result = null;
		Statement statement = null;

		try
		{
			statement = connection.createStatement();
			result = executeQuery(statement, String.format(selectByNameFormat, name));
			result.next();
			entry = CreateNotaryEntry(result);
		}
		catch ( SQLException ex )
		{
			Logger.getLogger(RepositoryProxy.class.getName()).log(Level.SEVERE, null, ex);
		}
		finally
		{
			if ( statement != null )
			{
				try
				{
					statement.close();
				}
				catch ( Exception e )
				{
				}
			}
		}
		return entry;
	}

	@Override
	public void connect()
	{
		connectionUrl = String.format(urlFormatString, address.getHostAddress(), port, database, username, password);
		try
		{
			connection = DriverManager.getConnection(connectionUrl);
		}
		catch ( SQLException ex )
		{
			Logger.getLogger(RepositoryProxy.class.getName()).log(Level.SEVERE, null, ex);
		}
	}

	@Override
	public void close()
	{
		throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
	}

	@Override
	public boolean contains( NotaryCertificate certificate )
	{
		ResultSet result = null;
		Statement statement = null;
		int results = -1;
		try
		{
			statement = connection.createStatement();
			result = executeQuery(statement, String.format(selectByNameFormat, certificate.getName(),
																										 certificate.getPublicKey()));
			result.next();
			results = result.getInt(1);

		}
		catch ( SQLException ex )
		{
			Logger.getLogger(RepositoryProxy.class.getName()).log(Level.SEVERE, null, ex);
		}
		finally
		{
			if ( statement != null )
			{
				try
				{
					statement.close();
				}
				catch ( Exception e )
				{
				}
			}
		}
		if ( results == 0 )
		{
			return false;
		}
		if ( results == 1 )
		{
			return true;
		}
		throw new IllegalStateException("more than one entry in database for provided NotaryCertificate");
	}

	@Override
	public void updateEntryStatus( NotaryEntry entry, EntryStatus newStatus )
	{
		Statement statement = null;
		try
		{
			statement = connection.createStatement();
			updateQuery(statement, prepareUpdateEntryStatusStatement(entry, newStatus));
		}
		catch ( SQLException ex )
		{
			Logger.getLogger(RepositoryProxy.class.getName()).log(Level.SEVERE, null, ex);
		}
		finally
		{
			if ( statement != null )
			{
				try
				{
					statement.close();
				}
				catch ( Exception e )
				{
				}
			}
		}
	}

	private static String prepareInsertStatement( NotaryEntry entry )
	{
		return String.format(insertEntryFormat, entry.getNotaryCertificate().getName(),
												 "0x" + ByteArrayConverter.bufferToHex(entry.getNotaryCertificate().getPublicKey()),
												 entry.getDateTime().toString("yyyy-MM-dd HH:mm:ss.SSS"), entry.getStatus().getNumVal());
	}

	private static String prepareUpdateEntryStatusStatement( NotaryEntry entry, EntryStatus newStatus )
	{
		return String.format(updateEntryStatus, newStatus.getNumVal(),
												 entry.getDateTime().toString("yyyy-MM-dd HH:mm:ss.SSS"),
												 entry.getNotaryCertificate().getName(),
												 "0x" + ByteArrayConverter.bufferToHex(entry.getNotaryCertificate().getPublicKey()),
												 entry.getStatus().getNumVal());
	}

	private NotaryEntry CreateNotaryEntry( ResultSet resultSet ) throws SQLException
	{
		NotaryEntry entry = new NotaryEntry();

		java.sql.Time sqlTime = resultSet.getTime(NotaryEntry.DATE_STRING);
		MutableDateTime dateTime = new MutableDateTime(sqlTime);
		DateTime date = new DateTime(resultSet.getDate(NotaryEntry.DATE_STRING));

		dateTime.setDayOfMonth(date.dayOfMonth().get());
		dateTime.setYear(date.year().get());
		dateTime.setMonthOfYear(date.monthOfYear().get());
		entry.setDateTime(new DateTime(dateTime));
		entry.setNotaryCertificate(new NotaryCertificate(resultSet.getString(NotaryEntry.NAME_STRING), resultSet.getBytes(
						NotaryEntry.PUBLIC_KEY_STRING)));
		entry.setStatus(EntryStatus.values()[resultSet.getByte(NotaryEntry.ENTRY_STATUS_STRING)]);

		return entry;
	}

	private ResultSet executeQuery( Statement statement, String query )
	{
		ResultSet result = null;
		try
		{
			statement.executeQuery(query);
			result = statement.executeQuery(query);
		}
		catch ( SQLException ex )
		{
			Logger.getLogger(RepositoryProxy.class.getName()).log(Level.SEVERE, null, ex);
		}
		return result;
	}

	private void updateQuery( Statement statement, String query )
	{
		try
		{
			statement.executeUpdate(query);
		}
		catch ( SQLException ex )
		{
			Logger.getLogger(RepositoryProxy.class.getName()).log(Level.SEVERE, null, ex);
		}
	}

	@Override
	public CertificateConflict checkConflict( NotaryCertificate certificate )
	{
		throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
	}
}
